"""
Case Type   : 函数支持指定并行相关参数
Case Name   : 嵌套游标入参场景--多个嵌套游标
Create At   : 2024/10/12
Owner       : @kyleze
Description :
    1.创建测试表
    2.函数声明为parallel_enable,且为srf函数或者管道函数
    3.入参游标为多个，调用函数是否支持并行
    4.清理环境
Expect      :
    1.成功
    2.成功
    3.支持并行
    4.成功
History     :
"""
import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant


class ParallelEnable(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.table_name = 't_parallel_test022'
        self.type_name = 'type_parallel_test022'
        self.package_name = 'pkg_parallel_test022'

    def test_paralle(self):
        step = '----step1:创建测试表 expect:成功----'
        self.log.info(step)
        create_sql = f"drop table if exists {self.table_name} cascade;" \
                     f"create table {self.table_name} " \
                     f"(employee_id number(22), department_id number, " \
                     f"first_name varchar2(30), last_name varchar2(30), " \
                     f"email varchar2(30), phone_number varchar2(30));" \
                     f"begin" \
                     f"   for i in 1..900000 loop" \
                     f"      insert into {self.table_name} values " \
                     f"(i, 60, 'abc', 'def', '223', '223');" \
                     f"end loop;" \
                     f"commit;" \
                     f"end;" \
                     f"create type {self.type_name} as (" \
                     f"    employee_id numeric(22, 0)," \
                     f"    department_id numeric," \
                     f"    first_name character varying(30)," \
                     f"    last_name character varying(30)," \
                     f"    email character varying(30)," \
                     f"    phone_number character varying(30));"
        create_res = self.primary_sh.execut_db_sql(create_sql)
        self.log.info(create_res)
        self.assertIn(
            self.constant.CREATE_TABLE_SUCCESS, create_res, '执行失败' + step)
        self.assertIn(self.constant.CREATE_TYPE_SUCCESS_MSG,
                      create_res, '执行失败' + step)
        ana_res = self.primary_sh.execut_db_sql(f"analyze {self.table_name};")
        self.log.info(ana_res)
        self.assertIn(
            self.constant.ANALYZE_SUCCESS_MSG, ana_res, '执行失败' + step)

        step = '----step2:函数声明为parallel_enable,且为SRF函数或者管道函数' \
               'expect:成功----'
        self.log.info(step)
        fun_sql = f"create or replace package {self.package_name} as" \
                  f"    function pkg_f (p sys_refcursor) return setof " \
                  f"{self.type_name} parallel_enable (partition p by any);" \
                  f"end {self.package_name};" \
                  f"create or replace package body {self.package_name} as " \
                  f"function pkg_f (p sys_refcursor) return setof " \
                  f"{self.type_name} parallel_enable (partition p by any) is" \
                  f"    out_rec {self.type_name} := {self.type_name}" \
                  f"(null, null, null, null, null, null);" \
                  f"begin" \
                  f"    loop" \
                  f"        fetch p into out_rec.employee_id, " \
                  f"out_rec.department_id, out_rec.first_name, " \
                  f"out_rec.last_name, out_rec.email, out_rec.phone_number;" \
                  f"        exit when p%notfound;" \
                  f"        return next out_rec;" \
                  f"    end loop;" \
                  f"    return;" \
                  f"end pkg_f;" \
                  f"end {self.package_name};"
        fun_res = self.primary_sh.execut_db_sql(fun_sql)
        self.log.info(fun_res)
        self.assertIn(self.constant.create_package_body_success_msg, fun_res,
                      '执行失败' + step)

        step = '----step3:调用函数是否支持并行 expect:支持并行----'
        self.log.info(step)
        tra_sql = f"explain (costs off) select /*+ set(query_dop 4) */ * " \
                  f"from {self.table_name};" \
                  f"explain (costs off) select count(*) from " \
                  f"{self.package_name}.pkg_f(cursor (select /*+ " \
                  f"set(query_dop 20) */ * from {self.table_name}));" \
                  f"explain (costs off) select /*+ set(query_dop 4) */ " \
                  f"count(*) from {self.package_name}.pkg_f(cursor (select" \
                  f" /*+ set(query_dop 4) */ * from {self.table_name}));" \
                  f"set query_dop = 4;" \
                  f"explain (costs off) select count(*) from " \
                  f"{self.package_name}.pkg_f(cursor (select * from " \
                  f"{self.table_name}));" \
                  f"select count(*) from {self.package_name}.pkg_f(cursor " \
                  f"(select * from {self.table_name}));" \
                  f"explain (costs off) select count(*) from " \
                  f"{self.package_name}.pkg_f(cursor (select /*+ " \
                  f"set(query_dop 10) */ * from {self.table_name}));"
        transaction_res = self.primary_sh.execut_db_sql(tra_sql)
        self.log.info(transaction_res)
        self.assertEqual(
            1, transaction_res.count("dop: 1/10"), '执行失败' + step)
        self.assertEqual(
            3, transaction_res.count("dop: 1/4"), '执行失败' + step)
        self.assertNotIn("dop: 1/20", transaction_res, '执行失败' + step)

    def tearDown(self):
        self.log.info('----清理环境----')
        table_res = \
            self.primary_sh.execut_db_sql(f'drop table {self.table_name};')
        self.log.info(table_res)
        type_res = self.primary_sh.execut_db_sql(
            f'drop type {self.type_name} cascade;')
        self.log.info(type_res)
        pkg_res = self.primary_sh.execut_db_sql(
            f'drop package {self.package_name};')
        self.log.info(pkg_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, table_res, '删表失败')
        self.assertIn(self.constant.DROP_TYPE_SUCCESS_MSG, type_res, '删表失败')
        self.assertIn(self.constant.drop_package_success_msg,
                      pkg_res, '删表失败')
        self.log.info(f'----{os.path.basename(__file__)} end----')
